<?php
/* 
    Name：FastPHP
    CopyRight: Mr.Fu 2019SR0832915
*/
namespace app\base;
/*
    数据库定义操作类
    Author:FastPHP
*/
use think\{Request,Db};

class DDL
{
    //MySQL数据表定义方法-获得数据中所有的表
    public function T_Get_Tables()
    {
        $dbconfig=config('database');
        $SQL="SELECT * FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA`='".$dbconfig['database']."'";
        $data=db::query($SQL);
        $arr=array();
        foreach ($data as $k=>$v) {
            $arr[$k]['TABLE_NAME']=$v['TABLE_NAME'];
            $arr[$k]['ENGINE']=$v['ENGINE'];
            $arr[$k]['TABLE_COMMENT']=$v['TABLE_COMMENT'];
        }
        return $arr;
    }
    //判断数据表是否存在
    public function T_Table_Exists($table)
    {
        $dbconfig=config('database');
        $table=$dbconfig['prefix'].$table;
        $list=$this->T_Get_Tables();
        
        $TABLE_NAME = array_column($list, 'TABLE_NAME');
        $index = array_search($table, $TABLE_NAME);

        if (isset($list[$index])) {
            return true;
        } else {
            return false;
        }
    }
    //获得数据表中所有字段
    public function T_Get_Fields($table, $profix=false)
    {
        $dbconfig=config('database');
        if (!$profix) {
            $table=$dbconfig['prefix'].$table;
        }
        
        $SQL="SELECT * FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA`='".$dbconfig['database']."' AND `TABLE_NAME`='$table'";
        
        $data=db::query($SQL);
        $arr=array();
        
        foreach ($data as $k=>$v) {
            $arr[$v['COLUMN_NAME']]['field_name']=$v['COLUMN_NAME'];
            $arr[$v['COLUMN_NAME']]['field_type']=$v['COLUMN_TYPE'];
            $arr[$v['COLUMN_NAME']]['extra']=$v['EXTRA'];
            $arr[$v['COLUMN_NAME']]['orderid']=$v['ORDINAL_POSITION'];
            $arr[$v['COLUMN_NAME']]['comment']=$v['COLUMN_COMMENT'];
        }
        return $arr;
    }
    //创建一个新数据表
    public function T_Create_Table($table, $engine='MyISAM', $comment='')
    {
        $dbconfig=config('database');
        $table_name=$dbconfig['prefix'].$table;
        if (in_array($table_name, $this->T_Get_Tables())) {
            return false;
        }
        $SQL="CREATE TABLE `".$table_name."` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`)) ENGINE=".$engine." AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='".$comment."'";
        return db::execute($SQL);
    }
    //在数据表中创建/更新 新字段
    public function T_Update_Field($table, $field, $type, $len, $NOTNULL='NOT NULL', $unsigned='unsigned', $after='', $comment='')
    {
        $dbconfig=config('database');
        $table_name=$dbconfig['prefix'].$table;
        if ($after!='') {
            $after=" AFTER `$after`";
        } else {
            $after="";
        }
        $fields=$this->T_Get_Fields($table);
        //定义需要设置字符集的数据类型
        $string_type=array('char','varchar','tinytext','text','mediumtext','longtext','enum','set');
        //定义不需要设置长度的数据类型
        $Not_Len_String=array('tinytext','text','mediumtext','longtext');
        if (in_array($type, $string_type)) {
            $charset=" CHARACTER SET utf8 COLLATE utf8_general_ci ";
        } else {
            $charset=" ";
        }
        
        if (!in_array($type, $Not_Len_String)) {
            $len_set="(".$len.") ";
        } else {
            $len_set=" ";
        }
        
        //判断字段是否存在
        if (isset($fields[$field])) {
            $SQL="ALTER TABLE `".$table_name."` CHANGE COLUMN `".$field."` `".$field."` ".$type.$len_set.$unsigned.$charset.$NOTNULL." COMMENT '".$comment."' ".$after;
        } else {
            $SQL="ALTER TABLE `".$table_name."` ADD COLUMN `".$field."` ".$type.$len_set.$unsigned.$charset.$NOTNULL." COMMENT '".$comment."' ".$after;
        }
        return db::execute($SQL);
    }
    //删除字段
    public function T_Drop_Field($table, $field)
    {
        $dbconfig=config('database');
        $table_name=$dbconfig['prefix'].$table;
        $fields=$this->T_Get_Fields($table);
        if (!isset($fields[$field])) {
            return false;
        }
        $SQL="ALTER TABLE `".$table_name."` DROP COLUMN `".$field."`";
        return db::execute($SQL);
    }
    //修改数据表名
    public function T_Table_Rename($src_name, $dst_name)
    {
        $dbconfig=config('database');
        $src_name=$dbconfig['prefix'].$src_name;
        $dst_name=$dbconfig['prefix'].$dst_name;
        
        $SQL="ALTER TABLE `$src_name` RENAME `$dst_name`";
        return db::execute($SQL);
    }
    //修改数据表引擎
    public function T_Table_Set_Engine($table, $engine='')
    {
        $dbconfig=config('database');
        $table=$dbconfig['prefix'].$table;
        
        $SQL="ALTER TABLE `".$table."` ENGINE=".$engine;
        
        return db::execute($SQL);
    }
    //修改数据表备注
    public function T_Table_Set_Comment($table, $comment='')
    {
        $dbconfig=config('database');
        $table=$dbconfig['prefix'].$table;
        $SQL="ALTER TABLE `".$table."` COMMENT '".$comment."' ";
        return db::execute($SQL);
    }
    //清空数据表
    public function T_Table_CleatData($table)
    {
        $dbconfig=config('database');
        $table=$dbconfig['prefix'].$table;
        $SQL="TRUNCATE TABLE `".$table."`";
        db::execute($SQL);
        db::execute("ALTER TABLE `$table` auto_increment = 1");
        return true;
    }
    //删除数据表
    public function T_Table_Drop($table)
    {
        $dbconfig=config('database');
        $table=$dbconfig['prefix'].$table;
        $SQL="DROP TABLE `".$table."`";
        return db::execute($SQL);
    }
}